using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using PetShop.BusinessEntities;
using PetShop.DataAccess.Utilities;

namespace PetShop.DataAccess.DAO
{
    public class ItemDAO
    {
        private const string SQL_SELECT_ITEM =
            "SELECT Item.ItemId, Item.Description, Item.InventoryQty, Item.ListPrice, Product.Name, Product.Descn FROM Item  INNER JOIN Product ON Item.ProductId = Product.ProductId WHERE Item.ItemId = @ItemId";

        private const string SQL_SELECT_ITEMS_BY_PRODUCT =
            "SELECT ItemId, Description, ListPrice, Product.Name FROM Item INNER JOIN Product ON Item.ProductId = Product.ProductId WHERE Item.ProductId = @ProductId";

        private const string PARM_ITEM_ID = "@ItemId";
        private const string PARM_PRODUCT_ID = "@ProductId";


        private const string SQL_TAKE_INVENTORY = "UPDATE item SET InventoryQty = InventoryQty - ";


        public int CurrentQtyInStock(string ItemId)
        {
            const string SQL_SELECT_INVENTORY = "SELECT InventoryQty FROM Item WHERE ItemId = @ItemId";

            int Qty = 0;
            var parm = new SqlParameter("@ItemId", SqlDbType.Char, 10);
            parm.Value = ItemId;

            Qty =
                Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.ConnString, CommandType.Text, SQL_SELECT_INVENTORY,
                                                        parm));

            return Qty;
        }


        public void TakeStock(InventoryItemInfo[] items)
        {
            SqlParameter[] inventoryParms;
            var cmd = new SqlCommand();


            using (var conn = new SqlConnection(SQLHelper.ConnString))
            {
                String strSQL = null;
                int i = 0;


                foreach (InventoryItemInfo item in items)
                {
                    strSQL = strSQL + SQL_TAKE_INVENTORY;

                    inventoryParms = GetInventoryParameters(i);

                    strSQL = strSQL + "@Quantity" + i + " WHERE ItemId = @ItemId" + i + ";";


                    inventoryParms[0].Value = item.Quantity;
                    inventoryParms[1].Value = item.ItemId;

                    foreach (SqlParameter parm in inventoryParms)
                        cmd.Parameters.Add(parm);
                    i++;
                }


                conn.Open();


                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;


                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }


        private static SqlParameter[] GetInventoryParameters(int i)
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_TAKE_INVENTORY + i);

            if (parms == null)
            {
                parms = new[]
                            {
                                new SqlParameter("@Quantity" + i, SqlDbType.Int),
                                new SqlParameter("@ItemId" + i, SqlDbType.Char, 10)
                            };

                SQLHelper.CacheParameters(SQL_TAKE_INVENTORY + i, parms);
            }

            return parms;
        }


        public List<ItemInfo> GetItemsByProduct(string productId)
        {
            var itemsByProduct = new List<ItemInfo>();

            var parm = new SqlParameter(PARM_PRODUCT_ID, SqlDbType.Char, 10);
            parm.Value = productId;


            using (
                SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnString, CommandType.Text,
                                                            SQL_SELECT_ITEMS_BY_PRODUCT, parm))
            {
                while (rdr.Read())
                {
                    var item = new ItemInfo(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetDecimal(2),
                                            rdr.GetString(3), null);

                    itemsByProduct.Add(item);
                }
            }

            return itemsByProduct;
        }


        public ItemInfo GetItem(string itemId)
        {
            ItemInfo item = null;


            var parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Char, 10);

            parm.Value = itemId;


            using (
                SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnString, CommandType.Text, SQL_SELECT_ITEM,
                                                            parm))
            {
                rdr.Read();
                item = new ItemInfo(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetInt32(2), rdr.GetDecimal(3),
                                    rdr.GetString(4), rdr.GetString(5));
            }
            return item;
        }
    }
}